<?php
$root = $_SERVER['DOCUMENT_ROOT'];
require_once "$root/libs/mysqli.class.php";
require_once "$root/class/admin.php";
require_once "$root/class/staff.php";

class DBconfigTest {
    public $DB;
    
    private $config = array(
        'host'=>'localhost',
        'user'=>'root',
        'pass'=>'root',
        'table'=>''
    );

    public function initalDB($newDBName){
        
        if($this->DB->checkDB_exist($newDBName)){
            //die("Database already existed, please change the name");
            return "$newDBName already exist, please change another name";
        }else{
            $TempCreateDb = "CREATE DATABASE $newDBName";
            $this->DB->query($TempCreateDb);
            $this->config['table']= $newDBName;
            //return "Create $newDBName successful";
            return "create DB successful";
             
        }
    }
    
    public function InitialRosterDutiesArray($dbName,$DutiesArray) {
        $sqlDuties1 = "DROP TABLE IF EXISTS `$dbName`.`duties`;";
        $this->DB->query($sqlDuties1);
        $sqlDuties2 = "CREATE TABLE `$dbName`.`duties` (`duty_id` int(10) NOT NULL AUTO_INCREMENT,`dutyName` varchar(50) NOT NULL,`description` varchar(500) NOT NULL,PRIMARY KEY (`duty_id`)) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
        $this->DB->query($sqlDuties2);
        foreach ($DutiesArray as $value) {
            $tempInsertDuties = "INSERT INTO `$dbName`.`duties` (`duty_id`, `dutyName`, `description`) VALUES (NULL, '$value','');";
            $this->DB->query($tempInsertDuties);
        }
    }
    
    
    //add roster id and need to adjust roster id class
    //******very important
    public function initialRosterTable($TempRosterArray,$TempSundaysArray,$dbName, $rosterId) {
        $TempCount = count($TempRosterArray);
        $TempString = '';
        for ($i = 0; $i < $TempCount; $i++) {
            $TempString = $TempString . "`$TempRosterArray[$i]` varchar(30) NOT NULL, ";
        }
        //echo '<br>'.$TempString.'<br>';
        $sql1 = "DROP TABLE IF EXISTS `$dbName`.`roster`;";
        $sql2 = "CREATE TABLE `$dbName`.`roster` (`roster_id` int(10) NOT NULL,`Date` date NOT NULL,$TempString PRIMARY KEY (`roster_id`,`Date`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;";

        $this->DB->query($sql1);
        $this->DB->query($sql2);

        //--start insert earch 
        //fist identify how many duties are included.
        //prepare the duties list string
        $TempDutiesString = '';
        $TempDutiesInsertEmptyString = '';
        for ($i = 0; $i < $TempCount; $i++) {
            $TempDutiesString = $TempDutiesString . ",`$TempRosterArray[$i]`";
            $TempDutiesInsertEmptyString = $TempDutiesInsertEmptyString . ",''";
        }
        foreach ($TempSundaysArray as $value) {
            $sqlSundayInsert = "INSERT INTO `$dbName`.`roster` (`roster_id`, `Date` $TempDutiesString) VALUES ($rosterId,'$value' $TempDutiesInsertEmptyString);";
            $this->DB->query($sqlSundayInsert);
        }
        $this->createStaffDataTable($dbName);
    }
    
     private function createStaffDataTable($DBName) {
        $sqlStaffData = "DROP TABLE IF EXISTS `$DBName`.`staffData`";
        $this->DB->query($sqlStaffData);
        $sqlStaffData1 = "CREATE TABLE `$DBName`.`staffData` ( `roster_id` int(20) NOT NULL, `staff_id` int(20) NOT NULL, `dutiesArray` varchar(200) NOT NULL DEFAULT '', `exceptDayArray` varchar(200) NOT NULL DEFAULT '', PRIMARY KEY (`roster_id`,`staff_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1";
        $this->DB->query($sqlStaffData1);
    }
    
    public function DBCheck($newDBName){
        if($this->DB->checkDB_exist($newDBName)){
            //die("Database already existed, please change the name");
            return "Database name is taken";
        }
    }
    
    public function checkAccount($email, $pw){
        $sql = "SELECT pw,email,admin FROM `roster_users`.`users` WHERE email = '$email'";
        $this->DB->query($sql);
        $result = $this->DB->get();
        if(count($result)>0){
            if($result[0]['pw']==$pw){
                if($result[0]['admin']==1){
                    return new admin($email, $pw);
                }else if($result[0]['admin']==0){
                    return new staff($email, $pw);
                }
                
            }else{
                echo 'pw is incorrect<br>';
                return false;
            }
            
        }else{
            echo "no such suer<br>";
            return false;
        }
    }
    
    public function registerAdmin($GiveName, $Surname, $Email, $PW){
        
        $emailUniqcheck = "SELECT `email` FROM `roster_users`.`users` WHERE `email` = '$Email'";
        $this->DB->query($emailUniqcheck);
        $result = $this->DB->get();
        if(count($result)==0){
            $TempSQLregistAdmin = "INSERT INTO `roster_users`.`users` (`user_id`, `gName`, `surname`, `email`, `pw`, `admin`, `rosterDBName`) VALUES (NULL, '$GiveName', '$Surname','$Email','$PW','1','')";
            $this->DB->query($TempSQLregistAdmin);
            echo 'Create successful';
        }else{
            echo "Email is already registed, please change another one";
        }
        
    }

        public function __construct() {
        $this->DB = new DB($this->config);

    }
    
    public function getDB(){
        return $this->DB;
    }
    
    public function updateRosterDBToUserDB($id,$newDB) {
        $TempUpdateRosterDBToUserDB = "UPDATE  `roster_users`.`users` SET  `rosterDBName` =  '$newDB' WHERE  `users`.`user_id` =$id";
        $this->DB->query($TempUpdateRosterDBToUserDB);
    }
    
    public function updateInitialState($id,$initialNumber) {
        if ($initialNumber < 0 | $initialNumber > 3) {
            return "illegial number";
        } else {
            $TempupateInitial = "UPDATE  `roster_users`.`users` SET  `initial` =  '$initialNumber' WHERE  `users`.`user_id` =$id";
            $this->DB->query($TempupateInitial);
        }
    }
    
}
?>